<ul class="Download"><li><a href="relinqish_the_pain/relinqish_the_pain.zip">Download relinqish_the_pain - 4.34 MB</a></li></ul>

<h2>Introduction</h2>

<p>
While a lot of free LINQ providers exist, they typically provide only basic functionality and fail at even mildly complex queries.
The reason for this is that .NET does not provide a LINQ provider with the LINQ query in the syntax most people use in their 
C# source code (from-where-select etc.), but instead in the form of an abstract syntax tree. Unfortunately this abstract syntax tree is not well suited to transforming the query expression to typical query languages (the implementation effort can easily reach several man months for a single specific LINQ provider; in addition only very little code reuse is possible between different LINQ providers based directly on the abstract syntax tree representation).

<p>
The <a href="http://en.wikipedia.org/wiki/Test-driven_development">TDD</a> developed open source <a href="http://www.re-motion.org">re-motion</a> framework supplies a commercial class LINQ provider library in the form of its re-linq component, that transforms the abstract syntax tree back to an object model that represents the LINQ query expression in a form much closer to typical query languages (In fact, re-linq can even transform hand-made abstract syntax trees into a semantically identical representation in query expression syntax). Using the re-linq provided representation, one can easily create powerful LINQ providers that emit queries in any number of query languages, including SQL, NHibernate's HQL, XQuery or Entity Framework's Entity SQL.

<p> 
Moreover, re-linq provides a flexible query model that lets you modify existing queries before generating code in a target query language. This allows you to create and modify queries from application code, but also to share transformation methods between different LINQ providers based on re-linq. For more information on this advanced topic see "Transforming queries" in the <a href="http://www.re-motion.org/blogs/mix/archive/2009/09/02/how-to-write-a-linq-provider-the-simple-way-again.aspx">"How to write a LINQ provider - the simple way"-blog-post</a>, which also goes into more detail on how to implement a general re-linq based LINQ provider.


<h2>Why re-linq</h2>

Existing LINQ provider implementations (such as Microsoft's Linq2SQL) typically work by transforming the LINQ tree representation to another 
query language in many small steps. This approach has several drawbacks: For instance it requires a wealth of up-front knowledge
about what tree combinations are actually possible, in addition to requiring a deep knowledge on the exact workings of how LINQ works internally. 
It also leads to implementations which are tightly coupled to the target query language, i.e. there is very little 
potential for generically reusable code parts: You have to start every LINQ provider from scratch.

<p>The re-linq open source LINQ provider library takes a different approach: It transforms the LINQ query into an internal C#-LINQ-like model, 
which is easier to understand and therefore to translate. re-linq requires the implementer to just override some methods in two visitor classes, which re-linq feeds with easily consumable parameters, while the whole tree traversal is handled in the background. This makes implementing any kind of LINQ provider a straightforward task.

<p>
You can find more on the inner workings of re-linq and its design philosophy in the <a href="http://www.re-motion.org/download/re-linq.pdf">re-linq whitepaper</a>. 



<h2>Background</h2>

<p>To understand this article you will need to have a basic knowledge of Microsoft <a href="http://en.wikipedia.org/wiki/Linq">LINQ</a> technology. 
The article describes how to implement a LINQ provider for <a href="http://en.wikipedia.org/wiki/NHibernate">NHibernate</a>, so
it is beneficial if you know the Hibernate Query Language (HQL); however, due to the syntactic similarity of HQL to SQL, the principles
shown in the sample can easily be translated into using re-linq to implement a SQL LINQ provider instead (even most of the code will be identical).
<p>Knowing the <a href="http://en.wikipedia.org/wiki/Visitor_pattern">Visitor pattern</a> is not required, but might aid in understanding the 
workings of re-linq.



<h2>Sample Queries</h2>
Below are some sample queries which our 4 hour re-linq for NHibernate implementation can handle. 

<p>Member Access and Multiple <code>from</code>-Statements (query returns all people who own their homes):
<pre lang="cs">
from l in NHQueryFactory.Queryable<Location> (session)
from p in NHQueryFactory.Queryable<Person> (session)
where (l.Owner == p) && (p.Location == l)
select p;
</pre>

<p>Joining and Sorting:
<pre lang="cs">
from p in NHQueryFactory.Queryable<Person> (session)
where p.Surname == "Oerson"
orderby p.Surname
join pn in NHQueryFactory.Queryable<PhoneNumber> (session) on p equals pn.Person
select pn;
</pre>

<p>Complex Expressions and Method Calling:
<pre lang="cs">
from l in NHQueryFactory.Queryable<Location> (session)
from p in NHQueryFactory.Queryable<Person> (session)
where (((((3 * l.ZipCode - 3) / 7)) == 9) && p.Surname.Contains ("M") && p.Location == l)  
select l;
</pre>



<h2>[Setup]</h2>

<p>Before you get started you will need to download and unzip the sample source code. 
If you want to execute the tests you then need to open the "hibernate.cfg.xml" file in the <code>NHibernate.ReLinq.Sample.UnitTests</code> project and adapt the DBMS connection data to your DBMS.
You will also need to create the sample DB given under Initial Catalog="...", which defaults to "NHibernate_ReLinq".

<p>Note that while re-linq is implemented following TDD principles, the sample code is only an integration test spike 
(It can be a good exercise for someone getting started with TDD to turn the spike into a TDD implementation). 



<h2>Fast Forward</h2>
If you are an expert and would rather dig into the code right now, here is a quick rundown of how the sample works:
<code>NHQueryFactory</code> creates  <code>NHQueryable&lt;T></code>, the NHibernate specific LINQ <code>IQueryable&lt;T></code>.
<code>NHQueryable&lt;T></code> creates and holds a <code>NHQueryExecutor</code>, which forms the bridge between re-linq and the
concrete query provider implementation through its <code>ExecuteScalar&lt;T></code>, <code>ExecuteSingle&lt;T></code> and 
<code>ExecuteCollection&lt;T></code> methods. For the NHibernate LINQ provider it suffices for <code>ExecuteScalar&lt;T></code> and
<code>ExecuteSingle&lt;T></code> to basically forward to <code>ExecuteCollection&lt;T></code>; <code>ExecuteCollection&lt;T></code> in turn uses a 
<code>HqlGeneratorQueryModelVisitor</code> to traverse the re-linq <code>QueryModel</code> and retrieve the resulting
HQL query string. 

<p>The <code>HqlGeneratorQueryModelVisitor</code> contains the specific LINQ provider code which creates the resulting HQL for the LINQ
<code>select</code>, <code>from</code>, <code>where</code>, <code>orderby</code>, <code>join</code>, etc commands through
its <code>VisitSelectClause</code>, <code>VisitWhereClause</code>, etc methods. 
Internally, it uses an (also LINQ-provider-specific) <code>HqlGeneratorExpressionTreeVisitor</code> to process different LINQ expressions, such as
binary expressions (<code>Equal</code>, <code>Add</code>,  <code>Multiply</code>,...), member access (<code>person.Location</code>), 
method calls (<code>person.FirstName.Contains("John")</code>), etc through its 
<code>VisitBinaryExpression</code>, <code>VisitMemberExpression</code>,... methods.
These two classes constitute the heart of any specific re-linq based LINQ provider.

<p><code>HqlGeneratorQueryModelVisitor</code> internally uses a <code>QueryPartsAggregator</code> to collect multiple <code>from</code>, 
<code>where</code>, etc statements and emit them in the correct HQL order. This is necessary, since LINQ is much more flexible than HQL 
(or SQL) with regards to the order of operations (<code>QueryPartsAggregator</code> might be moved to re-linq or the 
<a href="https://svn.re-motion.org/svn/Remotion-Contrib">re-motion contrib library</a> in the future).



<h2>The Test Domain</h2>
For our tests we use a simple test domain constisting of <code>Location</code>|s, <code>Person</code>|s and <code>PhoneNumber</code>|s.
 Each <code>Person</code> has exactly one <code>Location</code>
and can have an arbitrary number of <code>PhoneNumber</code>|s. You can find the classes in the test project under "DomainObjects", 
the NHibernate mapping
under "Mappings" (Note: You do not need to know how an NHibernate mapping works; suffice to say that that the mapping tells NHibernate 
how to persist instances of our test domain in the DBMS, and how to query them using its SQL-like HQL query language).



<h2>re-linq|ing NHibernate</h2>

<h3>select ... from Statement</h3>
After having set up the DB, open the file "IntegrationTests.cs" in <code>NHibernate.ReLinq.Sample.UnitTests</code>. It contains typical NHibernate
setup and teardown code, making sure that the tests do not interfere with each other. Scroll down until you come to the first <code>[Test]</code>,
<code>SelectFrom()</code>.
The test shows the simplest possible LINQ query:
<pre lang="cs">
from pn in NHQueryFactory.Queryable<PhoneNumber> (session) 
select pn;
</pre>
which simply returns all <code>PhoneNumber</code>|s created in the <code>SetupTestData()</code> method.

<p><code>NHQueryFactory</code> is the re-linq NHibernate LINQ queryable factory which allows domain objects of the passed generic type to
be queried through LINQ; e.g. for <code>PhoneNumber</code> instances:
<pre lang="cs">NHQueryFactory.Queryable&lt;PhoneNumber></pre>
In the case of NHibernate it has to be passed the <code>NHibernate.ISession</code> to use.

<p>For testing purposes, <code>CreateNHQuery(session, query.Expression).QueryString</code> gives the HQL query string 
corresponding to the LINQ <code>query</code>, which equals 
<pre lang="cs">"select pn from NHibernate.ReLinq.Sample.UnitTests.DomainObjects.PhoneNumber as pn"</pre>
Executing the <code>query</code> directly gives all 5 <code>PhoneNumber</code>|s in the test domain, as expected.

<p>The implementation shows that re-linq does most of the work for us. All we have to do is override <code>VisitMainFromClause</code> and 
<code>VisitSelectClause</code> in <code>HqlGeneratorQueryModelVisitor</code> (which derives from re-linq's <code>QueryModelVisitorBase</code>), with
the following trivial implementation. 
<pre lang="cs">
public override void VisitMainFromClause (MainFromClause fromClause, QueryModel queryModel)
{
  _queryParts.AddFromPart (fromClause);
  base.VisitMainFromClause (fromClause, queryModel);
}

public override void VisitSelectClause (SelectClause selectClause, QueryModel queryModel)
{
  _queryParts.SelectPart = GetHqlExpression (selectClause.Selector);
  base.VisitSelectClause (selectClause, queryModel);
}
</pre>
All the work on our parts is done by <code>QueryParts</code>, a simple helper class, which collects together 
different query components of the same type
(e.g. <code>from</code>-statements) and emits them in the correct order at the end.

<p>The <code>from</code>-statement is just stored:
<pre lang="cs">
public string SelectPart { get; set; }
</pre>

<p>
In the case of the <code>from</code>-statement, it also handles emission of the correct HQL-alias-syntax:
<pre lang="cs">
public void AddFromPart (IQuerySource querySource)
{
  FromParts.Add (string.Format ("{0} as {1}", GetEntityName (querySource), 
  querySource.ItemName));
}
</pre>
Note that this is easy to do, since re-linq already supplies us with the information we need.



<h3>where Statement</h3>

The next test introduces the <code>where</code>-statement and the equality comparison operator:

<pre lang="cs">
from pn in NHQueryFactory.Queryable<PhoneNumber> (session)
  where pn.CountryCode == "11111"
  select pn;
</pre>

The implementation is as straightforward as above:
<pre lang="cs">
public override void VisitWhereClause (WhereClause whereClause, 
  QueryModel queryModel, int index)
{
  _queryParts.AddWherePart (GetHqlExpression (whereClause.Predicate));
  base.VisitWhereClause (whereClause, queryModel, index);
}
</pre>

<code>GetHqlExpression</code> uses a <code>HqlGeneratorExpressionTreeVisitor</code> (which derives from re-linq's 
<code>ThrowingExpressionTreeVisitor</code>, aptly named since he throws when he encounters an expression for which no
handler has been implemented.), which handles e.g. binary expression evaluation; in the case of the equality operator:

<pre lang="cs">
protected override Expression VisitBinaryExpression (BinaryExpression expression)
{
  _hqlExpression.Append ("(");
  VisitExpression (expression.Left);

  switch (expression.NodeType)
  {
    case ExpressionType.Equal:
      _hqlExpression.Append (" = ");
      break;

    // handle additional binary expressions

    default:
      base.VisitBinaryExpression (expression); // throws
      break;
  }

  VisitExpression (expression.Right);
  _hqlExpression.Append (")");

  return expression;
}
</pre>
(Note: In production code, using a lookup table instead of the switch-case-construct will be the better choice).
<p>The <code>VisitExpression</code> method call is executed by re-linq code and does the required traversal of data structures.





<h3>Binary Expressions</h3>

Other binary expressions can be handled as trivially as the equality-operator:
<pre lang="cs">
    case ExpressionType.Equal:
      _hqlExpression.Append (" = ");
      break;

    case ExpressionType.AndAlso:
    case ExpressionType.And:
      _hqlExpression.Append (" and ");
      break;
    
    case ExpressionType.OrElse:
    case ExpressionType.Or:
      _hqlExpression.Append (" or ");
      break;

    case ExpressionType.Add:
      _hqlExpression.Append (" + ");
      break;

    case ExpressionType.Subtract:
      _hqlExpression.Append (" - ");
      break;

    // etc...
</pre>


<h3>join Clause</h3>
Implementing <code>join</code>-operations also takes only a few lines of code: 
<pre lang="cs">
public override void VisitJoinClause (JoinClause joinClause, 
  QueryModel queryModel, int index)
{
  _queryParts.AddFromPart (joinClause);
  _queryParts.AddWherePart (
      "({0} = {1})",
      GetHqlExpression (joinClause.OuterKeySelector), 
      GetHqlExpression (joinClause.InnerKeySelector));

  base.VisitJoinClause (joinClause, queryModel, index);
}
</pre>
Where again the <code>VisitJoinClause</code> call is executed by re-linq.


<h3>Method Calls</h3>

The following shows the implementation of a method call, in this case the test whether a string contains the given substring; the 
call to <code>Contains</code> is transformed into the SQL/HQL "<code>like %</code>&lt;substring><code>%</code>" syntax:
<pre lang="cs">
protected override Expression VisitMethodCallExpression (MethodCallExpression expression)
{
  var supportedMethod = typeof (string).GetMethod ("Contains");
  if (expression.Method.Equals (supportedMethod))
  {
    _hqlExpression.Append ("(");
    VisitExpression (expression.Object);
    _hqlExpression.Append (" like '%'+");
    VisitExpression (expression.Arguments[0]);
    _hqlExpression.Append ("+'%')");
    return expression;
  }
  else
  {
    return base.VisitMethodCallExpression (expression); // throws
  }
}
</pre>


<h3>Putting it all Together</h3>
The final step to finish the LINQ to NHibernate query transformation is the call to <code>QueryPartsAggregator</code> <code>BuildHQLString</code>,
which emits the information collected by the two visitors in the correct HQL order. The implementation, again, is straightforward 
(<code>SeparatedStringBuilder.Build</code> is a re-motion helper method which simply concatenates together strings from an enumerable, separating
them with its first argument):

<pre lang="cs">
public string BuildHQLString ()
{
  var stringBuilder = new StringBuilder ();

  if (string.IsNullOrEmpty (SelectPart) || FromParts.Count == 0)
    throw new InvalidOperationException (
      "A query must have a select part and at least one from part.");

  stringBuilder.AppendFormat ("select {0}", SelectPart);
  stringBuilder.AppendFormat (" from {0}", 
    SeparatedStringBuilder.Build (", ", FromParts));

  if (WhereParts.Count > 0)
    stringBuilder.AppendFormat (" where {0}", 
      SeparatedStringBuilder.Build (" and ", WhereParts));

  if (OrderByParts.Count > 0)
    stringBuilder.AppendFormat (" order by {0}", 
      SeparatedStringBuilder.Build (", ", OrderByParts));

  return stringBuilder.ToString ();
}
</pre>



<h3>The Rest</h3>
The examples above are about as complex as it gets, you should now have a good idea of how to go about
writing a LINQ provider based on re-linq. For further exploration please have a look at the
demo project (download link at top of page).


<h2>
Troubleshooting
</h2>
Q: If I execute the tests, all tests fail. What gives ?
<br />
A: Please check if you created the "NHibernate_ReLinq" DB in your DBMS and adapted the "hibernate.cfg.xml" file.


<h2>[Teardown]</h2>
In this article we have shown how the <a href="http://www.re-motion.org/">re-motion</a> re-linq library can be used to easily
implement feature rich LINQ providers with little effort. 
re-linq makes it easy to do so, since it encapsulates the complex LINQ parsing algorithms and provides a easily digestible model of the query, 
ready to be transformed by the two provider specific <code>HqlGeneratorQueryModelVisitor</code> and <code>HqlGeneratorExpressionTreeVisitor</code> visitor classes. 

<p><code>// May all your tests be green.</code>
<br/><code>Cleanup();</code>
<br/><code>// Ma;-)rkus</code>



<h2>History</h2>
<ul>
<li>2009.09.03 - Initial posting.
</ul>

